package com.qdu

import org.apache.spark.sql.SaveMode

object Test111 {
  def main(args: Array[String]): Unit = {

    import org.apache.spark.sql.SparkSession
    val spark = SparkSession.builder()
      .appName("HelloScala")
      .master("local[*]")
      .getOrCreate()

    //读取文件
    val csvDF = spark.read
      .format("jdbc")
      .option("url", "jdbc:mysql://localhost:3306/spark")
      .option("driver", "com.mysql.jdbc.Driver")
      .option("user", "root")
      .option("password", "root")
      .option("dbtable", "day_avg_temperature")
      .load()

    csvDF.createTempView("day_avg_temperature")
    //不同用户点赞量
    //使用子查询，内部查询出用户每个用户点赞数量
    val result =
      """
        |SELECT d.city ,SUBSTRING_INDEX(d.date,'-',1) year ,round(AVG(d.day_avg_temperature),2) avgt
        |    FROM day_avg_temperature d
        |    where   d.city ='青岛市'
        |    GROUP BY d.city ,SUBSTRING_INDEX(d.date,'-',1)
        |    ORDER BY year
      |""".stripMargin

    /*
    select H,round(sum(finish)/count(*),4) as finish_rate,
    round(sum(like)/count(*),4) as like_rate
    from DY
    group by H
    order by H desc


    --4.每个省最冷的月份以及最热的月份
    SELECT m.province ,m.`year` , min(m.month_avg_temperature) cold, MAX(m.month_avg_temperature) hot240
    FROM month_avg_temperature_op m
    group by m.province, m.`year` ;


    --5. 某地区最近几年温度变化
      SELECT d.city ,SUBSTRING_INDEX(d.`date`,'-',1) year ,round(AVG(d.day_avg_temperature),2) avgt
      FROM day_avg_temperature d
      where   d.city ='青岛市'
      GROUP BY d.city ,SUBSTRING_INDEX(d.`date`,'-',1)
      ORDER BY year;

    SELECT d.city ,SUBSTRING_INDEX(d.`date`,'-',1) year ,round(AVG(d.day_avg_temperature),2) avgt
    FROM day_avg_temperature d
    where   d.city ='北京市'
    GROUP BY d.city ,SUBSTRING_INDEX(d.`date`,'-',1)
    ORDER BY year;




     */




    val df = spark.sql(result)
    df.show(100,true)
//    df.write.format("jdbc")
//      .option("url","jdbc:mysql://localhost:3306/spark")
//      .option("driver","com.mysql.jdbc.Driver")
//      .option("user","root")
//      .option("password","root")
//      .option("dbtable","")
//      .mode(SaveMode.Append)
//      .save()

  }

}
